Re: Isnumeric function?
am 10.09.2004 04:21:53 von Josh
Theo,
> I was just thinking, wouldn't it be great if the pg community had a site
> where anyone could contribute their generic functions, or request for a
> particular function.
In theory, this is supposed to be a feature of pgFoundry.org. However, there
is a bug in gForge that prevents us from using it right now, and fixing the
bug is complicated.
--
Josh Berkus
Aglio Database Solutions
San Francisco
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Re: Isnumeric function?
am 10.09.2004 04:50:27 von Theo.Galanakis
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C496E0.EDB48D10
Content-Type: text/plain
Josh,
I agree with the machete technique, unfortunately The structure is inplace
and a work-around was required.
I created the Index you specified, however it chooses to run a seq scan on
the column rather than a Index scan. How can you force it to use that
Index..
CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
'^[0-9]{1,9}$';
select * from botched_table where content = 200::integer
Theo
-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Friday, 10 September 2004 4:46 AM
To: Theo Galanakis; pgsql-sql@postgresql.org
Subject: Re: [SQL] Isnumeric function?
Theo,
> Does anyone have any better suggestions???
Well, one suggestion would be to take a machete to your application.
Putting
key references and text data in the same column? Sheesh.
If that's not an option, in addition to the approach you've taken, you could
also do a partial index on the appropriate numeric values:
CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
'^[0-9]{1,9}$';
However, this approach may be more/less effective that the segregation
approach you've already taken.
--
Josh Berkus
Aglio Database Solutions
San Francisco
____________________________________________________________ __________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
------_=_NextPart_001_01C496E0.EDB48D10
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable
RE: [SQL] Isnumeric function?
Josh,
I agree with the machete technique, unfortunately The str=
ucture is inplace and a work-around was required.
I created the Index you specified, however it chooses to =
run a seq scan on the column rather than a Index scan. How can you force it=
to use that Index..
CREATE INDEX idx_content_numeric ON botched_table(content=
) WHERE content ~ '^[0-9]{1,9}$';
select * from botched_table where content =3D 200::intege=
r
Theo
-----Original Message-----
From: Josh Berkus [=
mailto:josh@agliodbs.com]
Sent: Friday, 10 September 2004 4:46 AM
To: Theo Galanakis; pgsql-sql@postgresql.org
Subject: Re: [SQL] Isnumeric function?
Theo,
> Does anyone have any better suggestions???
Well, one suggestion would be to take a machete to your a=
pplication. Putting
key references and text data in the same column? &n=
bsp; Sheesh.
If that's not an option, in addition to the approach you'=
ve taken, you could
also do a partial index on the appropriate numeric value=
s:
CREATE INDEX idx_content_numeric ON botched_table(content=
) WHERE content ~ '^[0-9]{1,9}$';
However, this approach may be more/less effective that th=
e segregation
approach you've already taken.
--
Josh Berkus
Aglio Database Solutions
San Francisco
____________________=
__________________________________________________
This email, including=
attachments, is intended only for the addressee
and may be confidential=
, privileged and subject to copyright. If you
have received this email =
in error, please advise the sender and delete
it. If you are not the in=
tended recipient of this email, you must not
use, copy or disclose its c=
ontent to anyone. You must not copy or
communicate to others content t=
hat is confidential or subject to
copyright, unless you have the consen=
t of the content owner.
|
------_=_NextPart_001_01C496E0.EDB48D10--
Re: Isnumeric function?
am 10.09.2004 07:39:33 von achill
O Theo Galanakis Ýãñáøå óôéò Sep 10, 2004 :
>
>
> Josh,
>
> I agree with the machete technique, unfortunately The structure is inplace
> and a work-around was required.
>
> I created the Index you specified, however it chooses to run a seq scan on
> the column rather than a Index scan. How can you force it to use that
> Index..
>
> CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
> '^[0-9]{1,9}$';
>
> select * from botched_table where content = 200::integer
EXPLAIN ANALYZE is your friend.
VACUUM [FULL] ANALYZE also.
Try with 200::text
In the end if there is an option for the planner to use the index
but he doesn't, then maybe its not worth it.
>
> Theo
> -----Original Message-----
> From: Josh Berkus [mailto:josh@agliodbs.com]
> Sent: Friday, 10 September 2004 4:46 AM
> To: Theo Galanakis; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Isnumeric function?
>
>
> Theo,
>
> > Does anyone have any better suggestions???
>
> Well, one suggestion would be to take a machete to your application.
> Putting
> key references and text data in the same column? Sheesh.
>
> If that's not an option, in addition to the approach you've taken, you could
>
> also do a partial index on the appropriate numeric values:
>
> CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
> '^[0-9]{1,9}$';
>
> However, this approach may be more/less effective that the segregation
> approach you've already taken.
>
>
--
-Achilleus
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Re: Isnumeric function?
am 10.09.2004 08:43:46 von gsstark
Theo Galanakis writes:
> I created the Index you specified, however it chooses to run a seq scan on
> the column rather than a Index scan. How can you force it to use that
> Index..
>
> CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
> '^[0-9]{1,9}$';
>
> select * from botched_table where content = 200::integer
You need to put a "and content ~ '^[0-9]{1,9}$'" in your query, the clause has
to match the clause in the partial index pretty closely.
perhaps you would find it convenient to make a view of
select * from botched_table where content ~ '^[0-9]{1,9}$'
and then just always select these values from that view.
Also the "::integer" is useless. It actually gets cast to text here anyways.
The index is on the text contents of the content column.
You might consider making the index a functional index on content::integer
instead. I suspect that would be faster and smaller than an index on the text
version of content:
slo=> create table botched_table (content text);
CREATE TABLE
slo=> create index idx_botched_table on botched_table ((content::integer)) where content ~ '^[0-9]{1,9}$';
CREATE INDEX
slo=> create view botched_view as (select content::integer as content_id, * from botched_table where content ~ '^[0-9]{1,9}$');
CREATE VIEW
slo=> explain select * from botched_view where content_id = 1;
QUERY PLAN
------------------------------------------------------------ ----------------------------
Index Scan using idx_botched_table on botched_table (cost=0.00..3.72 rows=3 width=32)
Index Cond: ((content)::integer = 1)
Filter: (content ~ '^[0-9]{1,9}$'::text)
(3 rows)
--
greg
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Re: Isnumeric function?
am 10.09.2004 09:02:18 von gsstark
Greg Stark writes:
> Theo Galanakis writes:
>
> > I created the Index you specified, however it chooses to run a seq scan on
> > the column rather than a Index scan. How can you force it to use that
> > Index..
> >
> > CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
> > '^[0-9]{1,9}$';
> >
> > select * from botched_table where content = 200::integer
>
> You need to put a "and content ~ '^[0-9]{1,9}$'" in your query, the clause has
> to match the clause in the partial index pretty closely.
Well this is weird. I tried to come up with a cleaner way to arrange this than
the view I described before using a function. But postgres isn't using the
partial index when it seems it ought to be available.
When I say it has to match "pretty closely" in this case I think it would have
to match exactly, however in the case of simple range operators postgres knows
how to figure out implications. Ie, "where a>1" should use a partial index
built on "where a>0".
slo=> create table test (a integer);
CREATE TABLE
slo=> create index idx_text on test (a) where a > 0;
CREATE INDEX
slo=> explain select * from test where a > 0;
QUERY PLAN
------------------------------------------------------------ ------------
Index Scan using idx_text on test (cost=0.00..17.50 rows=334 width=4)
Index Cond: (a > 0)
(2 rows)
slo=> explain select * from test where a > 1;
QUERY PLAN
------------------------------------------------------------ ------------
Index Scan using idx_text on test (cost=0.00..17.50 rows=334 width=4)
Index Cond: (a > 1)
(2 rows)
That's all well and good. But when I tried to make a version of your situation
that used a function I found it doesn't work so well with functional indexes:
slo=> create function test(integer) returns integer as 'select $1' language plpgsql immutable;
CREATE FUNCTION
slo=> create index idx_test_2 on test (test(a)) where test(a) > 0;
CREATE INDEX
slo=> explain select test(a) from test where test(a) > 0;
QUERY PLAN
------------------------------------------------------------ --------------
Index Scan using idx_test_2 on test (cost=0.00..19.17 rows=334 width=4)
Index Cond: (test(a) > 0)
(2 rows)
slo=> explain select test(a) from test where test(a) > 1;
QUERY PLAN
-------------------------------------------------------
Seq Scan on test (cost=0.00..25.84 rows=334 width=4)
Filter: (test(a) > 1)
(2 rows)
I can't figure out why this is happening. I would think it has something to do
with the lack of statistics on functional indexes except a) none of the tables
is analyzed anyways and b) the estimated row count is the same anyways.
--
greg
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html